package com.zjy.financialsystemjavafx.service;

import com.zjy.financialsystemjavafx.model.entity.Category;
import com.zjy.financialsystemjavafx.util.DatabaseUtil;

import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

/**
 * 分类服务层，负责收支分类的增删改查
 */
public class CategoryService {
    private final DataSource dataSource;

    public CategoryService() {
        this.dataSource = DatabaseUtil.getDataSource();
    }

    /**
     * 获取当前用户及系统默认分类
     */
    public List<Category> getCategories(int userId) throws Exception {
        String sql = "SELECT category_id, user_id, category_name, category_type, description FROM categories " +
                     "WHERE user_id IS NULL OR user_id = ? ORDER BY user_id, category_id";
        List<Category> list = new ArrayList<>();
        try (Connection conn = dataSource.getConnection();
             PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setInt(1, userId);
            try (ResultSet rs = ps.executeQuery()) {
                while (rs.next()) {
                    Category c = new Category(
                        rs.getInt("category_id"),
                        rs.getString("category_name"),
                        rs.getString("category_type"),
                        rs.getString("description")
                    );
                    list.add(c);
                }
            }
        }
        return list;
    }

    /**
     * 添加用户自定义分类
     */
    public boolean addCategory(int userId, String name, String type, String description) throws Exception {
        String sql = "INSERT INTO categories(user_id, category_name, category_type, description) VALUES (?, ?, ?, ?)";
        try (Connection conn = dataSource.getConnection();
             PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setInt(1, userId);
            ps.setString(2, name);
            ps.setString(3, type);
            ps.setString(4, description);
            return ps.executeUpdate() > 0;
        }
    }

    /**
     * 更新用户自定义分类
     */
    public boolean updateCategory(Category category, int userId) throws Exception {
        String sql = "UPDATE categories SET category_name = ?, category_type = ?, description = ? " +
                     "WHERE category_id = ? AND user_id = ?";
        try (Connection conn = dataSource.getConnection();
             PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setString(1, category.getCategoryName());
            ps.setString(2, category.getCategoryType());
            ps.setString(3, category.getDescription());
            ps.setInt(4, category.getCategoryId());
            ps.setInt(5, userId);
            return ps.executeUpdate() > 0;
        }
    }

    /**
     * 删除用户自定义分类
     */
    public boolean deleteCategory(int categoryId, int userId) throws Exception {
        String sql = "DELETE FROM categories WHERE category_id = ? AND user_id = ?";
        try (Connection conn = dataSource.getConnection();
             PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setInt(1, categoryId);
            ps.setInt(2, userId);
            return ps.executeUpdate() > 0;
        }
    }
} 